package DBUtil;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DBUtil {
    Connection con;
    Statement sql;
    PreparedStatement preSql;
    ResultSet rs;

    public void initDBC() throws  ClassNotFoundException,SQLException,IllegalAccessException,InstantiationException {
        Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
    }

    public Connection getConnection() throws InstantiationException,IllegalAccessException,ClassNotFoundException,SQLException {
        initDBC();
        String jdbcUrl = "jdbc:Access:////E:/Project/Graduation/Dym_password/Database.mdb";
        String user = "";
        String passwd = "";
        con = DriverManager.getConnection(jdbcUrl,user,passwd);
        return con;
    }

    //检测用户合法性
    public int loginVerify(String u_name, String u_pwd) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        int value = -1;
        String sqlStr = "select * from 用户信息";
        con = getConnection();
        sql = con.createStatement();
        rs = sql.executeQuery(sqlStr);
        while (rs.next()) {
            String userName = rs.getString("UserName");
            String userPwd = rs.getString("Password");
            if(u_name.equals(userName)&&!u_pwd.equals(userPwd)){
                value = 0;
                break;
            }
            if (u_name.equals(userName)&&u_pwd.equals(userPwd)) {
                value = 1;
                break;
            }
        }
        return value;
    }

    //获取可用令牌并呈现至注册界面
    public List<String> getKeyID() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        String sqlStr = "select * from 可用令牌";
        String id;
        List<String> ID = new LinkedList<String>();
        con = getConnection();
        sql = con.createStatement();
        rs = sql.executeQuery(sqlStr);
        while (rs.next()) {
            id = rs.getString(1);
            ID.add(id);
        }
        return ID;
    }

    //注册时验证用户是否已存在
    public boolean isExist(String u_name) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        boolean isExist = false;
        String sqlStr = "select * from 用户信息";
        con = getConnection();
        sql = con.createStatement();
        rs = sql.executeQuery(sqlStr);
        while (rs.next()) {
            String userName = rs.getString("UserName");
            if(userName.equals(u_name)) {
                isExist = true;
                break;
            }
        }
        return isExist;
    }

    //注册新用户
    public boolean registerVerify(String u_name, String u_pwd, String keyID) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        boolean registerValue = false;
        String sqlStr = "insert into 用户信息(UserName,Password,UserKeyID,UserLoginCount) values(?,?,?,?)";
//        String sqlStr = "insert into UserInfo(`UserName`，`Password`，`UserKeyID`，`UserLoginCount`) values ('qwe','123','123',123) while 1";
        con = getConnection();
        //sql = con.createStatement();
        preSql = con.prepareStatement(sqlStr);
        //rs = sql.executeQuery(sqlStr);
        preSql.setString(1,u_name);
        preSql.setString(2,u_pwd);
        preSql.setString(3,keyID);
        preSql.setInt(4,0);
        int row = preSql.executeUpdate();
        if(row > 0) {
            registerValue = true;
        }
        return registerValue;
    }

    //更新令牌库
    public void UpdateKeyList(String KeyID) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        String sqlStr = "delete from 可用令牌 where KeyID == '" + KeyID + "'";
        con = getConnection();
        preSql = con.prepareStatement(sqlStr);
        preSql.executeUpdate();
    }

    //根据用户名获取UserKeyID
    public String getUserKeyID(String u_name) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        String sqlStr = "select * from 用户信息";
        String UserKeyID = null;
        con = getConnection();
        sql = con.createStatement();
        rs = sql.executeQuery(sqlStr);
        while (rs.next()) {
           String username = rs.getString("UserName");
           if (username.equals(u_name)) {
               UserKeyID = rs.getString("UserKeyID");
               break;
           }
        }
        return UserKeyID;
    }

    //获取用登录次数
    public int GetLoginCount(String u_name) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        int UserLoginCount = 0;
        System.out.println("u_name = " + u_name);
        String sqlStr = "select * from 用户信息 where UserName = '" + u_name + "'";
        con = getConnection();
        sql = con.createStatement();
        rs = sql.executeQuery(sqlStr);
        if (rs.next()) {
            String username = rs.getString(1);
            System.out.println(username.equals(u_name));
            UserLoginCount = rs.getInt(4);
            System.out.println(UserLoginCount);
        }
        return UserLoginCount;
    }

    //更新用户登录次数
    public void UpdateLoginCount(String u_name, int NewLoginCount) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        System.out.println(u_name);
        String sqlStr = "Update 用户信息 set UserLoginCount = " + NewLoginCount + " where UserName = '" + u_name + "'";
        con = getConnection();
        preSql = con.prepareStatement(sqlStr);
        preSql.executeUpdate();
    }

    //删除用户
    public void delete() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        String sqlStr = "delete from 用户信息 where UserName != 'wangzihong'";
        con = getConnection();
        preSql = con.prepareStatement(sqlStr);
        preSql.executeUpdate();
    }

}
